/**
 * Database 数据库操作基类
 * @module database
 */
class Database {
  static engine;

  async #query(sql, ...params) {
    // 兼容 sql 模板字符串
    if (Array.isArray(sql))
      sql = String.raw({ raw: sql }, ...Array(params.length).fill("?"));
    else {
      if (params.length == 1 && Array.isArray(params[0])) [params] = params;
    }
    return await this.run(sql, params);
  }

  /**
   * 查询多条数据
   * @param {string | string[]} sql 查询语句
   * @param  {...any} params
   * @returns { Promise<any[]> }
   */
  async findMany(sql, ...params) {
    return this.#query(sql, ...params);
  }

  /**
   * 查询单条数据
   * @param {string | string[]} sql 查询语句
   * @param  {...any} params
   * @returns { Promise<any> }
   */
  async findOne(sql, ...params) {
    const [rs] = await this.#query(sql, ...params);
    return rs;
  }

  /**
   * 插入数据
   * @param {string | string[]} sql 插入语句
   * @param  {...any} params
   * @returns {Promise<object>}
   */
  async insert(sql, ...params) {
    const { insertId } = await this.#query(sql, ...params);
    if (Array.isArray(sql))
      sql = String.raw({ raw: sql }, ...Array(params.length).fill("?"));
    return this.findOne(
      `select * from ${getTableName(sql)} where id = ${insertId}`
    );
  }

  /**
   * 更新数据
   * @param {string | string[]} sql 更新语句
   * @param  {...any} params
   * @returns {Promise<object>}
   */
  async update(sql, ...params) {
    const { affectedRows } = await this.#query(sql, ...params);
    return affectedRows;
  }

  /**
   * 删除数据
   * @param {string | string[]} sql 删除语句
   * @param  {...any} params
   * @returns {Promise<object>}
   */
  async delete(sql, ...params) {
    const { affectedRows } = await this.#query(sql, ...params);
    return affectedRows;
  }

  /**
   * 关闭数据库连接
   * @returns {Promise<void>}
   */
  async end() {}
}

/**
 * MySql 数据库操作子类型
 * @class MySql
 * @extends Database
 */
class MySql extends Database {
  static engine = "mysql";

  constructor(pool) {
    super();
    this.pool = pool;
  }

  static async create(config) {
    const mysql = await import("mysql2");
    return new MySql(mysql.createPool(config));
  }

  async run(sql, params) {
    return new Promise((resolve, reject) => {
      this.pool.execute(sql, params, (err, results) => {
        if (err) return reject(err);
        resolve(results);
      });
    });
  }

  async end() {
    this.pool.end();
  }
}

/**
 * Sqlite 数据库操作子类型
 * @class Sqlite
 * @extends Database
 */
class Sqlite extends Database {
  static engine = "sqlite";

  constructor(db) {
    super();
    this.db = db;
    this.sql = db.createTagStore();
  }

  static async create(filename) {
    const sqlite = await import("node:sqlite");
    const { DatabaseSync } = sqlite;
    const db = new DatabaseSync(filename);
    return new Sqlite(db);
  }

  async findMany(sql, ...params) {
    return this.sql.all(sql, ...params);
  }

  async findOne(sql, ...params) {
    return this.sql.get(sql, ...params);
  }

  async insert(sql, ...params) {
    const { lastInsertRowid } = this.sql.run(sql, ...params);
    if (Array.isArray(sql)) [sql] = sql;

    return this.findOne(
      `select * from ${getTableName(sql)} where id = ${lastInsertRowid}`
    );
  }

  async update(sql, ...params) {
    const { changes } = this.sql.run(sql, ...params);
    return changes;
  }

  async delete(sql, ...params) {
    const { changes } = this.sql.run(sql, ...params);
    return changes;
  }

  async end() {
    this.db.close();
  }
}

/**
 * 获取插入语句中的表名
 * @param {string} sql 插入语句
 * @returns {string}
 */
const regex = /INSERT\s+INTO\s+([^\s\(]+)/i;
function getTableName(sql) {
  const match = sql.match(regex);
  return match ? match[1] : null;
}

/**
 * 创建数据库连接实例
 *
 * @param {string} config 数据库连接字符串：mysql://localhost:3306
 * @returns {Promise<Database>}
 */
export function createDatabase(config) {
  const { DATABASE_URL = config } = process.env;
  const [schema, uri] = DATABASE_URL.split("://");
  if (["sqlite", "sqlite3"].includes(schema)) {
    return Sqlite.create(uri);
  } else if (schema === "mysql") {
    return MySql.create(DATABASE_URL);
  }
  console.log(`Unsupported database schema: ${schema}`);
  process.exit(1);
}

// export const db = await createDatabase();
